package com.wl.dzt.sys.core.dao.curd.mysql;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.wl.dzt.sys.core.dao.mate.DataMateBase;
import com.wl.dzt.sys.core.dao.mate.DataMateColumn;
import com.wl.dzt.sys.core.dao.mate.DataMateTable;
import com.wl.dzt.sys.core.dao.select.Select;
import com.wl.dzt.sys.core.dao.select.SelectMate;
import com.wl.dzt.sys.core.dao.select.SelectSqlBuilder;
import com.wl.dzt.sys.core.dao.select.order.Sort;
import com.wl.dzt.sys.core.dao.select.term.Term;
import com.wl.dzt.sys.core.dao.select.term.TermOptions;
import com.wl.dzt.sys.util.CollectionUtil;
import com.wl.dzt.sys.util.FieldUtil;
import org.springframework.stereotype.Component;


/**
 * @author wl_code@163.com
 * @version 1.0
 * @date 2018/7/5/005
 * @description mysql查询构建器
 */
@Component
public class SelectSqlBuilderForMysql implements SelectSqlBuilder {

    /**
     * <b>构建mysql排序</b>
     * <p>
     * 创建mysql的排序sql
     * </p>
     *
     * @param selectMate
     * @param clazz
     * @param sorts
     */
    public void builderOrder(SelectMate selectMate, Class<?> clazz, List<Sort> sorts) {
        if (!sorts.isEmpty()) {
            List<String> allOrders = new ArrayList<>();
            sorts.forEach(sort -> {
                String field = sort.getField();
                DataMateColumn column = DataMateBase.getTable(clazz).getColumn(field);
                allOrders.add(String.format(" %s  %s ", column.asFieldName(field), sort.getType()));
            });
            selectMate.setOrderSql(String.format(" order by  %s ", CollectionUtil.join(allOrders, ",")));
        }
    }

    /**
     * <b>构建mysql分页</b>
     * <p>
     * 创建mysql的分页sql
     * </p>
     *
     * @param selectMate
     * @param start
     * @param limit
     */
    public void builderPage(SelectMate selectMate, Integer start, Integer limit) {
        selectMate.setPageSql(limit > 0 ? ("( %s ) limit " + start + "," + limit) : "");
    }

    /**
     * <b>构建mysql查询条件</b>
     * <p>
     * 创建mysql的查询sql<br>
     * 并且根据字段，排序以及查询条件动态的创建关联
     * </p>
     *
     * @param selectMate
     * @param clazz
     * @param terms
     */
    public void builderWhere(SelectMate selectMate, Class<?> clazz, List<Term> terms) {
        if (!terms.isEmpty()) {
            List<String> wheres = new ArrayList<String>();
            List<Object> args = new ArrayList<Object>();
            terms.forEach(term -> {
                Map<String, Object> map = TermOptions.paserTerms(clazz, term);
                wheres.add(map.get("sql").toString());
                String op = term.getOp();
                if (!"isNull".equals(op) && !"!isNull".equals(op) && !"in".equals(op) && !"!in".equals(op)) {
                    Object obj = map.get("val");
                    if (TermOptions.isDate(obj + "") && "<=".equals(term.getOp())) {
                        obj = TermOptions.paserDateLq(obj + "");
                    } else if (TermOptions.isDate(obj + "") && ">=".equals(term.getOp())) {
                        obj = TermOptions.paserDateTq(obj + "");
                    }
                    args.add(obj);
                }
            });
            selectMate.setArgs(args);
            selectMate.setWhereSql(CollectionUtil.join(wheres, " and "));
        }
    }

    /**
     * <b>构建mysql查询字段</b>
     * <p>
     * 构建mysql查询字段
     * </p>
     *
     * @param selectMate
     * @param clazz
     * @param fields
     */
    public void builderFields(SelectMate selectMate, Class<?> clazz, Set<String> fields) {
        fields.addAll(selectMate.getRefIds());
        List<String> sqlFields = new ArrayList<>();
        fields.forEach(field -> {
            DataMateColumn column = DataMateBase.getTable(clazz).getColumn(field);
            if (column != null) {
                String asSql = column.asFieldName(field) + " as " + field.replaceAll("[.]", "_");
                sqlFields.add(asSql);
            }
        });
        selectMate.setFieldSql(CollectionUtil.join(sqlFields, ","));
    }

    /**
     * <b>初始化</b>
     * <p>
     * 初始化全部的查询字段<br>
     * 初始化查询的基础表
     * </p>
     *
     * @param selectMate
     * @param select
     */
    public void init(SelectMate selectMate, Select select) {
        Set<String> allFields = new HashSet<>();
        String tableName = DataMateBase.getTable(select.getClazz()).getTableName();
        select.getOrders().forEach(s -> allFields.add(s.getField()));
        select.getWheres().forEach(t -> allFields.add(t.getField()));
        select.getFields().forEach(f -> allFields.add(f));
        selectMate.setAllFields(allFields);
        selectMate.setBaseTable(tableName);
    }

    /**
     * <b>构建引用</b>
     * <p>
     * 根据所传递的字段生成 left join语句
     * </p>
     *
     * @param selectMate
     * @param clazz
     */
    public void buiderRef(SelectMate selectMate, Class<?> clazz) {
        Set<String> all = selectMate.getAllFields(), rf = new LinkedHashSet<String>();
        List<String> references = new ArrayList<>();
        all.stream().filter(field -> field.contains(".")).forEach(field -> {
            String ary[] = field.split("[.]");
            Class<?> refClazz = clazz;
            for (int i = 0; i < ary.length - 1; i++) {
                String now = ary[i];
                refClazz = FieldUtil.getFieldByCascade(refClazz, now).getClazz();
                DataMateTable srcMateTable = DataMateBase.getTable(clazz), tagerMateTable = DataMateBase.getTable(refClazz);
                if (i == 0) {
                    String refField = srcMateTable.getColumn(now).asFieldName(now);
                    String refTable = tagerMateTable.getTableName();
                    String asRefTable = srcMateTable.asTableName(now);
                    String asTable = refTable + " as " + asRefTable;
                    String asField = refField + " = " + tagerMateTable.asTableName(now) + ".id ";
                    rf.add(String.format(" left join %s on %s ", asTable, asField));
                    selectMate.addRefId(now + ".id");
                    references.add(refTable);
                }
                if (i + 2 != ary.length) {
                    String key = ary[i + 1];
                    List<String> ls = new ArrayList<>();
                    for (int j = 0; j < i + 2; j++) {
                        ls.add(ary[j]);
                    }
                    String me = CollectionUtil.join(ls, ".");
                    DataMateTable cassadeTable = DataMateBase.getTable(FieldUtil.getFieldByCascade(refClazz, key).getClazz());
                    String refTable = cassadeTable.getTableName();
                    String asTable = refTable + " as " + me.replace(".", "_");
                    String asField = tagerMateTable.getColumn(key).asFieldName(me) + " = " + me.replace(".", "_") + ".id";
                    rf.add(String.format(" left join %s on %s ", asTable, asField));
                    List<String> fields = new ArrayList<>();
                    for (int j = 0; j < i + 1; j++) {
                        fields.add(ary[j]);
                    }
                    selectMate.addRefId(CollectionUtil.join(fields, ".") + ".id");
                }
            }
        });
        if (rf.size() == 0) {
            return;
        }
        selectMate.setRefSql(CollectionUtil.join(rf, " "));
    }

    /**
     * <b>组装sql</b>
     * <p>
     * 将各处解析后的结果封装入对象后返回
     * </p>
     *
     * @param select
     * @return
     */
    public SelectMate assembleAll(Select select) {
        Class<?> clazz = select.getClazz();
        SelectMate selectMate = new SelectMate();
        this.init(selectMate, select);// 构建查询表格
        this.builderOrder(selectMate, clazz, select.getOrders());// 构建排序
        this.builderPage(selectMate, select.getStart(), select.getLimit());// 构建分页
        this.builderWhere(selectMate, clazz, select.getWheres());// 构建条件
        this.buiderRef(selectMate, clazz);// 构建引用
        this.builderFields(selectMate, clazz, select.getFields());// 构建字段
        return selectMate;
    }

}
